package com.wuhongyu.entity;

import lombok.*;

import javax.persistence.*;
import java.io.Serializable;


/**
 * 本地查询,包括客户构成分析,和客户服务分析
 */
@SqlResultSetMappings({
        @SqlResultSetMapping(
                name = "Cons",
                columns = {
                        @ColumnResult(name = "id", type = Integer.class),
                        @ColumnResult(name = "items", type = String.class),
                        @ColumnResult(name = "number", type = Integer.class)
                }
        )

})

@NamedNativeQueries({
        @NamedNativeQuery(
                name = "Dictionary.findByGrade",
                query = "select dictionary.value id, dictionary.items items, count(customer.name) number from dictionary " +
                        "left join customer on dictionary.items = customer.grade where type = '企业客户等级'" +
                        "group by dictionary.items;",
                resultSetMapping = "Cons"
        ),
        @NamedNativeQuery(
                name = "Dictionary.findByCredit",
                query = "select dictionary.value id, dictionary.items items, count(customer.name) number from dictionary " +
                        "left join customer on dictionary.items = customer.credit where type = '客户信用度'" +
                        "group by dictionary.items;",
                resultSetMapping = "Cons"
        ),
        @NamedNativeQuery(
                name = "Dictionary.findBySatisfied",
                query = "select dictionary.value id, dictionary.items items, count(customer.name) number from dictionary " +
                        "left join customer on dictionary.items = customer.satisfied where type = '客户满意度'" +
                        "group by dictionary.items;",
                resultSetMapping = "Cons"
        ),
        @NamedNativeQuery(
                name = "Dictionary.findServerByYear",
                query = "select dictionary.value id ,dictionary.items items ,count(server.name) number from dictionary " +
                        "left join server on dictionary.items = server.type and DATE_FORMAT(server.createtime,'%Y') = ? where dictionary.type = '服务类型'" +
                        "group by dictionary.items;",
                resultSetMapping = "Cons"
        ),
        @NamedNativeQuery(
                name = "Dictionary.findServer",
                query = "select dictionary.value id ,dictionary.items items ,count(server.name) number from dictionary " +
                        "left join server on dictionary.items = server.type where dictionary.type = '服务类型'" +
                        "group by dictionary.items;",
                resultSetMapping = "Cons"
        )
})

/**
 *字典表
 */
@Entity
@Data
public class Dictionary implements Serializable {
    private static final long serialVersionUID = -8937440327684373047L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String type;
    //条目
    private String items;
    private String value;
    private Integer status;

}